package com.fingard.dsp.bank.directbank.cbs01;


import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;

import com.fingard.constant.Format;
import com.fingard.dsp.bank.directbank.bankInterface.ISinglePay;
import com.fingard.dsp.bank.reqrespobj.single.*;
import com.fingard.dsp.bank.reqrespobj.single.detail.ReqPayRecDetail;
import com.fingard.dsp.bank.reqrespobj.single.detail.RetAtsSglDetail;
import com.fingard.text.SqlHelper;
import com.fingard.text.StringHelper;

public class CBS01SinglePay extends CBS01Base implements ISinglePay {
	
	//单笔支付，不需采用流处理方式
	public RetAtsSingle d_1908_SglPay(ReqSingle p_req1908) throws Exception{
		//返回ATS的对象
		RetAtsSingle retAts = new RetAtsSingle(p_req1908.reqHeader);
		
		RetAtsSglDetail retDetail = new RetAtsSglDetail();
		retDetail.reqSeqID = p_req1908.singleDetail.askNumber;
		
		Statement st =null;
		Connection conn = null;
		try{
			conn = getConnection();
		}
		catch(Exception e){
			if(conn != null){
				conn.close();
			}
			e.printStackTrace();
			retDetail.respCode = "0";
			retDetail.transState = "fail";
			retDetail.payInfoCode = "fail";
			retDetail.respInfo = "连接数据库失败："+e.getMessage();
			retAts.writeRetDetail(retDetail);
			return retAts;
		}
		try{
			conn.setAutoCommit(false);
			st =conn.createStatement();
			
			String tableName = getBankConfig().getValueAsString("tableName1908");
			String insertSql = buildEachPaySql(tableName, p_req1908.singleDetail);
			this.WriteBankLogLn2("执行insert sql语句：" + insertSql);
			int affRows = st.executeUpdate(insertSql);
			
			retDetail.respCode = "0";
			
			if(affRows == 1){
				conn.commit();
				retDetail.transState = "submitted";
				retDetail.payInfoCode = "submitted";
				retDetail.respInfo = "提交成功";
			}
			else{
				conn.rollback();
				retDetail.transState = "fail";
				retDetail.payInfoCode = "fail";
				retDetail.respInfo = "提交失败，写入表影响行数：" + affRows;
			}
		}
		catch (Exception e) {
			conn.rollback();
			retDetail.respCode = "-1";
			retDetail.respInfo = e.toString().replace("\"", " ");
			if(retDetail.respInfo.indexOf("ORA-00904")>0 || retDetail.respInfo.indexOf("ORA-01400")>0){
				retDetail.respCode = "0";
				retDetail.transState = "fail";
				retDetail.payInfoCode = "fail";
			}
			e.printStackTrace();
		}
		finally{
			if(st!=null){
				st.close();
			}
			if(conn!=null){
			    conn.close();
			}
		}
		if(retDetail.respCode.equals("-1") && StringHelper.isNullOrEmpty(retDetail.reqSeqID)){
			retDetail.reqSeqID = p_req1908.singleDetail.askNumber;
		}
		retAts.writeRetDetail(retDetail);
		return retAts;
	}

	@Override
	public RetAtsSingleBat d_1908BAT_SglBatPay(ReqSingleBat p_req1908BAT) throws Exception {
		return null;
	}

	public String buildEachPaySql(String p_tableName, ReqPayRecDetail p_atsDetail) throws Exception{
		Date timeNow = new Date();
		StringBuilder tmpSbSql = new StringBuilder();
		StringBuilder tmpSbValues = new StringBuilder();
		tmpSbSql.append("insert into "+p_tableName+" (ERP_PAYMENT_ID, ");
		tmpSbValues.append(" values ("+SqlHelper.varToSql(p_atsDetail.askNumber)+", ");					
		tmpSbSql.append("RECORD_STATUS, ");
		String RECORD_STATUS = "Available";
		tmpSbValues.append(SqlHelper.varToSql(RECORD_STATUS) + ", ");
		tmpSbSql.append("C_RECORD_STATUS, ");
		tmpSbValues.append("'"+Format.DateTimeFormat.format(timeNow)+"', ");
		tmpSbSql.append("PAYMENT_TYPE_ID, ");
		tmpSbValues.append("'202', ");
		tmpSbSql.append("PAYMENT_BUSTYPE_ID, ");
		tmpSbValues.append("'0', ");
		//
		
		//C_RP_TYPE 取交易类型， 这个字段招行CBS不取，只是用来业务人员查询。
		tmpSbSql.append("C_RP_TYPE, ");
		//收付类型：参考表v_fin_rptype定义
		//String C_RP_TYPE = getBankConfig().getMapValueByKey("rptype1908", p_atsDetail.useType);
		String C_RP_TYPE = p_atsDetail.payType;
		tmpSbValues.append(SqlHelper.varToSql(C_RP_TYPE)+ ", ");
			
		tmpSbSql.append("PAYMENT_METHOD_TYPE_ID, ");
		tmpSbValues.append("'3', ");
		
		tmpSbSql.append("PAYMENT_ACCOUNTS, ");
		tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.payAct)+ ", ");
		
		tmpSbSql.append("CURRENCY_TYPE, ");
		tmpSbValues.append(SqlHelper.varToSql(this.convertCurToBank(p_atsDetail.payCur))+ ", ");
		
		tmpSbSql.append("DEPOSIT_BANK_NAME, ");
		tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.getOppBankLocation())+ ", ");
		tmpSbSql.append("DEPOSIT_ACCOUNTS_NAME, ");
		tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.recName)+ ", ");
		tmpSbSql.append("DEPOSIT_ACCOUNTS, ");
		tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.recAct)+ ", ");
		
		tmpSbSql.append("DEPOSIT_BANK_TYPE, ");
		String DEPOSIT_BANK_TYPE = getBankConfig().getMapValueByKey("bankMapping", p_atsDetail.recBankCode);
		if (StringHelper.isNullOrEmpty(DEPOSIT_BANK_TYPE)) {
			DEPOSIT_BANK_TYPE = "OTH";
		}
		tmpSbValues.append(SqlHelper.varToSql(DEPOSIT_BANK_TYPE)+ ", ");
			
		String[] pcity = p_atsDetail.getOppProvinceAndCity();
		tmpSbSql.append("DEPOSIT_PROVINCE, ");
		tmpSbValues.append(SqlHelper.varToSql(pcity[0])+ ", ");
		tmpSbSql.append("DEPOSIT_CITY, ");
		tmpSbValues.append(SqlHelper.varToSql(pcity[1])+ ", ");
		tmpSbSql.append("DEPOSIT_MOBILE, ");
		tmpSbValues.append("'', ");
		tmpSbSql.append("DEPOSIT_EMAIL, ");
		tmpSbValues.append("'', ");
		tmpSbSql.append("CITY_FLAG, ");
		if(p_atsDetail.sameCity.equals("1")){
			tmpSbValues.append("'0', ");
		}
		else{
			tmpSbValues.append("'1', ");
		}
			
		tmpSbSql.append("PRIORITY_FLAG, ");
		if(p_atsDetail.paySpd.equals("1") && p_atsDetail.payBankCode.equals("102")){
			tmpSbValues.append("'Y', ");
		}
		if(p_atsDetail.paySpd.equals("1") && p_atsDetail.payBankCode.equals("308")){
			tmpSbValues.append("'N', ");
		}
			
		tmpSbSql.append("OPERATION_TYPE, ");
		tmpSbValues.append("'3', ");
		
		//CBS中间表的 bank_number: 付款行和收款行都为工行时，要求为0000
		tmpSbSql.append("BANK_NUMBER, ");
		String BANK_NUMBER = "";
		if(p_atsDetail.payBankCode.equals("102") && p_atsDetail.recBankCode.equals("102")){
			BANK_NUMBER = "0000";
		}
		else{
//			BANK_NUMBER = getBankConfig().getMapValueByKey("bankMapping"+p_atsDetail.payBankCode, p_atsDetail.recBankCode);
			BANK_NUMBER = "  ";
		}
		tmpSbValues.append(SqlHelper.varToSql(BANK_NUMBER)+ ", ");
		
		tmpSbSql.append("UNION_BANK_NUMBER, ");
		tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.cnapsCode)+ ", ");
		
		tmpSbSql.append("AMOUNT, ");
		tmpSbValues.append(p_atsDetail.payAmount+ ", ");
		//CBS中间表的PURPOSE 取 reccheckinfo（抽档中间表传过来的paymentcode 前端唯一业务号目前存这个字段）
		tmpSbSql.append("PURPOSE, ");
		if(p_atsDetail.payBankCode.equals("102")){
			tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.useType)+ ", ");
		}
		if(p_atsDetail.recBankCode.equals("308")){
			tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.sourceNote)+ ", ");
		}
		
		//CBS中间表的abstract 取 交易单表的purpose
		tmpSbSql.append("ABSTRACT, ");
		if(p_atsDetail.payBankCode.equals("102")){
			tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.sourceNote)+ ", ");
		}
		if(p_atsDetail.payBankCode.equals("308")){
			tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.useType)+ ", ");
		}
		//Net_bank_ept_date和Net_bank_ept_time 产险未写值
		//tmpSbSql.append("NETBANK_EPT_DATE, ");
		//tmpSbValues.append(SqlHelper.varToSql(Format.Date10Format.format(timeNow))+ ", ");
		//tmpSbSql.append("NETBANK_EPT_TIEM, ");
		//tmpSbValues.append(SqlHelper.varToSql(Format.Time8Format.format(timeNow))+ ", ");
		
		//p_atsDetail.askNumber
		//RECORD_STATUS
		//p_atsDetail.payAct
		//p_atsDetail.recAct
		//AMOUNT
		//CENTER_TRANS_ID
		String AMOUNT = Format.DecimalAmtFormat.format(new BigDecimal(p_atsDetail.payAmount));
		String CENTER_TRANS_ID = "";
		String CHECK_CODE = getCHECK_CODE(p_atsDetail.askNumber,RECORD_STATUS,p_atsDetail.payAct,p_atsDetail.recAct,AMOUNT,CENTER_TRANS_ID);
		tmpSbSql.append("CHECK_CODE, ");
		tmpSbValues.append(SqlHelper.varToSql(CHECK_CODE)+ ", ");
		tmpSbSql.append("C_SEGMENT8, ");
		tmpSbValues.append(SqlHelper.varToSql(p_atsDetail.sourceNote)+ ", ");
		//tmpSbSql.append("ERP_COMMENT, ");
		//tmpSbValues.append(SqlHelper.varToSql(p_reqBat.reqBatchNO)+ ", ");
		tmpSbSql.append("VERSION" + ")");
		tmpSbValues.append("0" + ")");
		
		tmpSbSql.append(tmpSbValues.toString());
		return tmpSbSql.toString();
	}
	
		
	//1944 状态查询
	public RetAtsSingle d_1944_QrySglPay(ReqQrySingle p_req1944) throws Exception{
		//返回ATS的对象
		RetAtsSingle retAts = new RetAtsSingle(p_req1944.reqHeader);
		
		RetAtsSglDetail retDetail = new RetAtsSglDetail();
		Connection conn = null;
		PreparedStatement pre = null;
	    ResultSet result = null;
		try{
			conn = getConnection();
			String tableName = getBankConfig().getValueAsString("tableName1908");
			String sql = "select * from "+tableName+" where ERP_PAYMENT_ID=?";
			this.WriteBankLogLn2("执行select sql语句：select * from "+tableName+" where ERP_PAYMENT_ID='"+p_req1944.askNumber+"'");
	        pre = conn.prepareStatement(sql);
	        pre.setString(1, p_req1944.askNumber);
	        result = pre.executeQuery();
	        
	        if(result.next()){
	        	/*
	        	 * RECORD_STATUS	VARCHAR(20)	是	指令状态：
Available，等待CBS系统读取数据；
Delegated，CBS系统已读取数据；
Accepted，CBS系统已经受理；
Success，付款成功；
Failed，所有非成功的终态。
Returnticket，银行退票 CBS4.6新增状态

TRANS_STATUS	VARCHAR(60)	否	支付业务的状态，格式为“代码;对应状态描述”，例如：
“CP;待中心处理”，支付指令状态为Accepted，Failed，Success才有效。Returnticket，银行退票 CBS4.6新增状态
CBS系统回填的信息，
TRANS_STATUS_CODE	CHAR(2)	否	支付业务状态码，上述例子中此字段值为“CP”。
CBS系统回填的信息。

CBS_COMMENT	VARCHAR(255)	否	备注信息，CBS系统回填的信息。
	        	 * */
	        	//ERP_PAYMENT_ID,RECORD_STATUS,PAYMENT_ACCOUNTS,DEPOSIT_ACCOUNTS,AMOUNT,CENTER_TRANS_ID,
	        	//TRANS_STATUS,CHECK_CODE
	        	retDetail.respCode = "0";
				retDetail.reqSeqID = result.getString("ERP_PAYMENT_ID");
				retDetail.respInfo = result.getString("TRANS_STATUS");
				retDetail.payInfoCode = result.getString("TRANS_STATUS_CODE");
                retDetail.transState = result.getString("RECORD_STATUS");
	        }
	        else{
	        	result.close();
	        	result = null;
	        	pre.close();
	        	pre = null;
	        	sql = "select count(*) from "+tableName+" where ERP_PAYMENT_ID=?";
		        pre = conn.prepareStatement(sql);
		        pre.setString(1, p_req1944.askNumber);
		        result = pre.executeQuery();
		        if(result.next()){
		        	if(result.getInt(1) == 0){
		        		retDetail.respCode = "0";
						retDetail.reqSeqID = p_req1944.askNumber;
						retDetail.respInfo = "记录不存在";
						retDetail.payInfoCode = "notExist";
		                retDetail.transState = "notExist";
		        	}
		        	else{
		        		retDetail.respCode = "-1";
		        		retDetail.respInfo = "数据库记录与预期不符";
		        	}
		        }
		        else{
	        		retDetail.respCode = "-1";
	        		retDetail.respInfo = "查询数据库记录出现意外情况";
	        	}
	        }
		}
		catch (Exception e) {
			retDetail.respCode = "-1";
			retDetail.respInfo = e.getMessage();
			e.printStackTrace();
		}
		finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (conn != null)
	                conn.close();
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		
		retAts.writeRetDetail(retDetail);
		return retAts;
	}

	@Override
	public RetAtsSingleBat d_1944BAT_QrySglBatPay(ReqQrySingleBat p_req1944BAT) throws Exception {
		return null;
	}
}
